package com.andcard.sql;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.andcard.obj.Card;
import com.andcard.obj.CountData;
import com.andcard.obj.DatabaseStats;
import com.andcard.util.MiscUtil;
import com.andcard.util.Reference;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DbAdapter {

	private String pathDatabase;
	private SQLiteDatabase database;
	private boolean readOnly = true;
	private boolean reverse = true;
	
	// compiled statement
	private SQLiteStatement stmtInsertCard;
	private SQLiteStatement stmtDeleteCard;
	private SQLiteStatement stmtUpdateCardData;
	private SQLiteStatement stmtUpdateCardStatusOk;
	private SQLiteStatement stmtUpdateCardStatusKo;	
	
	// current List of cards
	private List<Card> listCard = null;
	
	public Card getCard(int counter){
		return listCard.get(counter);
	}
	
	public List<Card> getListCard(){
		return listCard;
	}
	
	public boolean isDatabaseReadOnly(){
		return readOnly;
	}
	
	public boolean isDatabaseReverse(){
		return reverse;
	}
	
	public String getPathDatabase(){
		return pathDatabase;
	}
	
	// simply check database existence
	public boolean isDatabaseOpen(){
		if (database == null || database.isOpen() == false){
			return false;
		}
		return true;
	}

	public boolean openDatabase(String path, boolean reverse, boolean readOnly, boolean all){

		if (this.isDatabaseOpen() == true){
			Log.d(Reference.TAG, "DbAdapter.openDatabase - close database");
			database.close();
		}
		
		this.pathDatabase = path;
		Log.d(Reference.TAG, "DbAdapter.openDatabase - try to open database -path " + path + " -reverse " + reverse + " -readOnly - " + readOnly + " -all " + all);
		// no cursor factory for these first release ;)
		try{
			database = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
			
			if (database == null || database.isOpen() == false){
				Log.d(Reference.TAG, "DbAdapter.openDatabase - impossible to open database");
				return false;
			}
			
			// recreate compiled statement
			createCompileStatement();
			
		}catch (SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return false;
		}
		
		return true;
	}

	public List<Card> loadDatabase(String path, boolean reverse, boolean readOnly, boolean all){
		
		Log.d(Reference.TAG, "DbAdapter.loadDatabase() -path " + path + " -reverse " + reverse + " -readOnly " + readOnly);
		
		boolean ret = openDatabase(path, reverse, readOnly, all);
		
		if (ret == false){
			return null;
		}
		
		// we have to update daily cards
		// every cards older than sysdate are moved to sysdate
		if (readOnly == false && markDailyCards() == false){
			Log.d(Reference.TAG, "DbAdapter.loadDatabase - no daily cards marked");
			return null;
		}

		this.readOnly = readOnly;
		this.reverse = reverse;

		
		// getting daily cards
		return getDailyCards(reverse, all);
		
	}
	
	public List<Card> getDailyCards(boolean reverse, boolean all){
		
		Log.d(Reference.TAG, "DbAdapter.getDailyCards() -reverse " + reverse + " -all " + all);
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.getDailyCards() - no database open");
			return null;
		}
		
		try{
			Cursor c;
			SimpleDateFormat isoFormat = new SimpleDateFormat(Reference.DATE_FORMAT_ISO);
	
			if (all){
				c = database.rawQuery("select * from card",null);
			}else{
				c = database.rawQuery("select * from card where next_date = ?",new String[]{isoFormat.format(new Date())});	 
			}
			
			listCard = buildList(c);
			return listCard;
			
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return null;
		}
	}

	public List<Card> buildList(Cursor c) throws SQLException{
		
		Log.d(Reference.TAG, "DbAdapter.buildList()");
		
		// check cursor is not null or cursor is not empty
		if (c == null || c.isBeforeFirst() == false){
			Log.d(Reference.TAG, "DbAdapter.getDailyCards() - cursor is null or empty");
			return null;
		}
		
		listCard = new LinkedList<Card>();
		
		while (c.moveToNext()){
			
			Card card;
			
			if (reverse == false){
				card = new Card(c.getInt(Reference.COL_ID),c.getString(Reference.COL_QUESTION),c.getString(Reference.COL_ANSWER), c.getString(Reference.COL_NEXT_DATE),c.getInt(Reference.COL_ITERATION), c.getInt(Reference.COL_OK_ANSWER), c.getInt(Reference.COL_KO_ANSWER), c.getInt(Reference.COL_LAST_ANSWER));
			}else{
				// reverse mode
				card = new Card(c.getInt(Reference.COL_ID),c.getString(Reference.COL_ANSWER),c.getString(Reference.COL_QUESTION), c.getString(Reference.COL_NEXT_DATE),c.getInt(Reference.COL_ITERATION), c.getInt(Reference.COL_OK_ANSWER), c.getInt(Reference.COL_KO_ANSWER), c.getInt(Reference.COL_LAST_ANSWER));
			}
		
			listCard.add(card);
		}

		c.close();
	
		return listCard;		
	}
	
	public List<Card> getFilterCards(String code){
		
		Log.d(Reference.TAG, "DbAdapter.getFilterCards() -code " + code);
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.getFilterCards - no database open");
			return null;
		}
		
		try{
			Cursor c;
			if (code == null){
				c = database.rawQuery("select * from card", null);
			}else if (code.equals("KO")){
				c = database.rawQuery("select * from card where last_answer = 0",null);
			}else{
				return null;
			}
			
			listCard =  buildList(c);
			return listCard;
			
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return null;
		}
	}
	
	public boolean markDailyCards(){
		
		Log.d(Reference.TAG, "DbAdapter.markDailyCards()");
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.markDailyCards - no database open");
			return false;
		}
		
		try{
			database.execSQL("update card set next_date = current_date where next_date < current_date");
		}catch (SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return false;
		}
		
		return true;
	}
	
	public boolean createDatabase(String path){
		
		// create database
		Log.d(Reference.TAG, "DbAdapter.createDatabase - try to create database path " + path);
		try{
			database = SQLiteDatabase.openOrCreateDatabase(path, null);
			
			if (this.isDatabaseOpen() == false){
				Log.d(Reference.TAG, "DbAdapter.createDatabase - impossible to create database");
				return false;
			}
			
			// create tables
			Log.d(Reference.TAG, "DbAdapter.createDatabase - try to create table card");
			database.execSQL("create table card (id integer primary key autoincrement, question varchar(255), answer varchar2(255), next_date varchar2(10) default CURRENT_DATE, iteration integer default 0, ok_answer integer default 0, ko_answer integer default 0, last_answer integer default 1)");
			
			// recreate compiled statement
			createCompileStatement();
			
			return true;
		}catch (SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
		}
		return true;
	}
	
	public DatabaseStats getDatabaseStats(){
		
		Log.d(Reference.TAG, "DbAdapter.getDatabaseStats");
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.getDatabaseStats - no database open");
			return null;
		}
		
		try{
			Cursor c = database.rawQuery("select count(*), sum(ok_answer), sum(ko_answer) from card", null);
			
			// check cursor is not null or cursor is not empty
			if (c == null || c.isBeforeFirst() == false){
				Log.d(Reference.TAG, "DbAdapter.getDatabaseStats() - cursor ok/ko is null or empty");
				return null;
			}

			// first item
			c.moveToNext();
			
			DatabaseStats dbStatsData = new DatabaseStats();
			dbStatsData.setCountRecords(c.getInt(0));
			dbStatsData.setOkAnswers(c.getInt(1));
			dbStatsData.setKoAnswers(c.getInt(2));
			// closing cursor			
			c.close();
			
			// take number execution for date
			c = database.rawQuery("select next_date, count(*) cnt from card group by next_date", null);
			
			if (c == null || c.isBeforeFirst() == false){
				Log.d(Reference.TAG, "DbAdapter.getDatabaseStats() - cursor dateCount is null or empty");
				return null;
			}

			// first item
			c.moveToNext();
			
			CountData[] countData = new CountData[c.getCount()];
			
			for(int x = 0; x < c.getCount(); x++){
				countData[x] = new CountData(c.getString(0),c.getInt(1));
				c.moveToNext();
			}
		
			// closing cursor
			c.close();
			
			dbStatsData.setCountData(countData);
			dbStatsData.setReverse(reverse);
			dbStatsData.setReadOnly(readOnly);
			
			return dbStatsData;
			
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return null;
		}
	}
	
	public boolean deleteCard(int idx){
		
		Log.d(Reference.TAG, "DbAdapter.deleteCard");
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.deleteCard - no database open");
			return false;
		}
		
		if (readOnly == true){
			Log.d(Reference.TAG, "DbAdapter.deleteCard - database read only");
			return false;
		}
		
		try{
			stmtDeleteCard.clearBindings();
			stmtDeleteCard.bindLong(1, listCard.get(idx).getId());
			stmtDeleteCard.execute();
			Log.d(Reference.TAG, "DbAdapter.deleteCard - delete card id " + listCard.get(idx).getId());
			return true;
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return false;
		}
	}
	
	public boolean insertCard(String question, String answer){
		
		Log.d(Reference.TAG, "DbAdapter.insertCard -question " + question + " -answer " + answer);
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.insertCard - no database open");
			return false;
		}
		
		if (readOnly == true){
			Log.d(Reference.TAG, "DbAdapter.insertCard - database read only");
			return false;
		}
		
		try{
			stmtInsertCard.clearBindings();
			stmtInsertCard.bindString(1, question);
			stmtInsertCard.bindString(2, answer);
			stmtInsertCard.executeInsert();
			return true;
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return false;
		}
	}
	
	public boolean updateCardData(int idx, String question, String answer){
		
		Log.d(Reference.TAG, "DbAdapter.updateCardData -idx " + idx + " -question " + question + " -answer " + answer);
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.changeCard - no database open");
			return false;
		}
		
		if (readOnly == true){
			Log.d(Reference.TAG, "DbAdapter.changeCard - database read only");
			return false;
		}
		
		try{
			stmtUpdateCardData.clearBindings();
			stmtUpdateCardData.bindString(1, question);
			stmtUpdateCardData.bindString(2, answer);
			stmtUpdateCardData.bindLong(3,listCard.get(idx).getId());
			stmtUpdateCardData.execute();
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return false;
		}
		
		return true;
		
	}
	
	public boolean updateCardStatus(int idx, boolean okAnswer){

		Log.d(Reference.TAG, "DbAdapter.updateCardStatus -idx " + idx + " -okAnswer " + okAnswer);
		
		if (this.isDatabaseOpen() == false){
			Log.d(Reference.TAG, "DbAdapter.updateCardStatus - no database open");
			return false;
		}
		
		if (readOnly == true){
			Log.d(Reference.TAG, "DbAdapter.updateCardStatus - database read only");
			return false;
		}
		
		Calendar c1 = Calendar.getInstance();
		SimpleDateFormat isoFormat = new SimpleDateFormat(Reference.DATE_FORMAT_ISO);		
		
		try{
			if (okAnswer){
				// ok
				c1.add(Calendar.DATE, (int)Math.pow(2,listCard.get(idx).getIteration()));
				stmtUpdateCardStatusOk.clearBindings();
				stmtUpdateCardStatusOk.bindString(1, isoFormat.format(c1.getTime()));
				stmtUpdateCardStatusOk.bindLong(2, listCard.get(idx).getId());
				stmtUpdateCardStatusOk.execute();
			}else{
				// ko
				c1.add(Calendar.DATE, 1);
				stmtUpdateCardStatusKo.clearBindings();
				stmtUpdateCardStatusKo.bindString(1, isoFormat.format(c1.getTime()));
				stmtUpdateCardStatusKo.bindLong(2, listCard.get(idx).getId());
				stmtUpdateCardStatusKo.execute();
			}
		}catch(SQLException ex){
			Log.e(Reference.TAG, MiscUtil.getStackTrace(ex));
			return false;			
		}
		
		return true;
	}
	
	
	
	// compile statement
	public void createCompileStatement(){
		stmtInsertCard = database.compileStatement("insert into card (question, answer) values (?,?)");
		stmtDeleteCard = database.compileStatement("delete from card where id = ?");
		stmtUpdateCardData = database.compileStatement("update card set question = ?, answer = ? where id = ?");
		stmtUpdateCardStatusOk = database.compileStatement("update card set next_date = ?, iteration = iteration + 1, ok_answer = ok_answer + 1, last_answer = 1 where id = ?");
		stmtUpdateCardStatusKo = database.compileStatement("update card set next_date = ?, iteration = 0, ko_answer = ko_answer + 1, last_answer = 0 where id = ?");
	}
}
